/**
 * Database connection. This class provides methods to:
 *  - connect to a database
 *  - close the database
 *  - execute queries that return result sets
 *  - execute queries that don't return result sets
 *  - create prepared statements
 *  - get data from a ResultSet
 *
 */
package mainpackage;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;

public class DatabaseConnection {

    /**
     * Default static configuration variables
     */
    private static final String HOST = "localhost:3307";
    private static final String DATABASE = "local";
    private static final String UID = "root";
    private static final String PASS = "";
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://" + HOST + "/" + DATABASE;
    /**
     * Connection object
     */
    private Connection connect = null;
    /**
     * Singleton Design Pattern DatabaseConnection instance.
     */
    private static DatabaseConnection instance = null;
    /**
     * Default properties for connecting to the Hockey database.
     */
    private final static Properties setup = new Properties();

    static {
        setup.setProperty( "driver", "com.mysql.jdbc.Driver" );
        setup.setProperty( "url", "jdbc:mysql://localhost:3307/hockeydatabase" );
        setup.setProperty( "uid", "root" );
        setup.setProperty( "pass", "" );
    }

    /**
     * Singleton-Design Pattern. We do not want to establish a connection to the database each time we transition
     * between different views, we also do not want to have to pass a database connection object throughout the
     * presentation layer.
     * @return The static initialized DatabaseConnection instance.
     */
    public static DatabaseConnection instance() {
        if( instance == null ) {
            try {
                instance = new DatabaseConnection( setup );
            } catch( SQLException sqle ) {
                ExceptionUtils.handleException( sqle, DatabaseConnection.class );
                System.exit( 1 );
            }
        }
        return instance;
    }

    /**
     * Constructor to specify database location
     */
    public DatabaseConnection( Properties props ) throws SQLException {
        if( props == null ) {
            connect( DRIVER, URL, UID, PASS );
        } else {
            connect( props.getProperty( "driver" ), props.getProperty( "url" ),
                     props.getProperty( "uid" ), props.getProperty( "pass" ) );
        }
    }

    /**
     * Connect to the database
     * @param driver Driver to use for connecting to the DBMS
     * @param pass Password to access the database
     * @param uid User Id to access the database
     * @param url URL to the database
     * @throws java.sql.SQLException
     */
    private void connect( String driver, String url, String uid, String pass ) throws SQLException {
        try {
            Class.forName( driver );
            connect = DriverManager.getConnection( url, uid, pass );
        } catch( ClassNotFoundException e ) {
            ExceptionUtils.handleException( e, this.getClass() );
        }
    }

    /**
     * Close the connection
     * @throws java.sql.SQLException
     */
    public void close() throws SQLException {
        if( !connect.isClosed() ) {
            connect.close();
        }
    }

    /**
     * Execute a query that doesn't return a result
     * @param query The string query
     * @throws java.sql.SQLException
     */
    public void executeNonQuery( String query ) throws SQLException {
        Statement st = connect.createStatement();
        st.execute( query );
    }

    /**
     * Execute a query that returns success or failure (update, insert, delete).
     * @param query The string query
     * @return The result of running the update query.
     * @throws SQLException
     */
    public int executeUpdateQuery( String query ) throws SQLException {
        Statement st = connect.createStatement();
        return st.executeUpdate( query );
    }

    /**
     * Execute a query that returns a result
     * @param query the query
     * @return the ResultSet
     * @throws java.sql.SQLException
     */
    public ResultSet executeQuery( String query ) throws SQLException {
        Statement st = connect.createStatement();
        ResultSet rs = st.executeQuery( query );
        return rs;
    }

    /**
     * Create a new prepared statement that allows auto-generated keys to be returned.
     * @param query
     * @return
     * @throws java.sql.SQLException
     */
    public PreparedStatement newPreparedStatement( String query ) throws SQLException {
        return connect.prepareStatement( query, Statement.RETURN_GENERATED_KEYS );
    }

    /**
     * Returns a new prepared statement using a sql statement and an array of parameters.
     * @param stmnt The sql statement to use for the prepared statement.
     * @param objs The parameters to inject into the prepared statement.
     * @return A prepared statement.
     * @throws SQLException
     */
    public PreparedStatement buildStmnt( String stmnt, Object... objs ) throws SQLException {
        PreparedStatement ret = newPreparedStatement( stmnt );
        int rowIndx = 0;
        do {
            ret.setObject( (rowIndx + 1), objs[rowIndx] );
            rowIndx++;
        } while( rowIndx < objs.length );
        return ret;
    }

    /**
     * Generic method to return all data of a ResultSet within a collection. Column headers can be included.
     * @param rs The ResultSet to return all data from.
     * @param columns Flag to indicate if we want the column headers returned with the data.
     * @return A collection of Rows of data (implemented as a collection of strings).
     * @throws SQLException
     */
    public ArrayList<ArrayList<String>> getData( ResultSet rs, boolean columns ) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        ArrayList<ArrayList<String>> ret = new ArrayList<ArrayList<String>>();
        ArrayList<String> temp = new ArrayList<String>();
        if( columns ) {
            for( int i = 1; i <= rsmd.getColumnCount(); i++ ) {
                temp.add( rsmd.getColumnName( i ) );
            }
            ret.add( temp );
        }
        while( rs.next() ) {
            for( int i = 1; i <= rsmd.getColumnCount(); i++ ) {
                temp.add( rs.getString( i ) );
            }
            ret.add( temp );
        }
        return ret;
    }

    /**
     * Gets a single row's data. This method should only be used when the ResultSet is known to only return one row.
     * @return The result set's data as a collection of strings.
     * @throws SQLException
     */
    public ArrayList<String> getSingleRow( ResultSet rs ) throws SQLException {
        ArrayList<String> ret = new ArrayList<String>();
        if( rs.next() ) {
            for( int i = 1; i <= rs.getMetaData().getColumnCount(); i++ ) {
                ret.add( rs.getString( i ) );
            }
        }
        return ret;
    }
}
